Hi everyone.
And later we will learn what all formatting can be done to make our data look good.
Hey you got a sample table that no formatting has been done.
Data in this format is not very appealing and you should not show such data in your presentations let's
try to learn tools which make it beautiful.
Let's first highlight the edit of our data.
David intellect the cells which have the headers and really click on the B icon which baldy take that
we have selected.
You can see the weight of the extended selected area as we increased you can compare student with the
student 1 reading below.
Now we will select any student one and others to read names from the data and make them into italics
click on the icon and you can see that all the student names have been changed to italics.
Now we will boldly average then in the 14 to but to do that instead of selecting all these and leaving
on bold icon we will select the first row which is a header row and click on format.
What this does is it copied all the formatting of these selected cells.
And when we click on the other cells that we want to format it copy that formatting on the destination
is as you can see it is now also in bold format.
Next what we'll do is we will underline the total and percentage headers so we select those two so then
click on underlying.
You can see that these two figures are no underlying now we will change the format for this table select
the whole it will
go to the dropdown option that calibrated it in and we will to link into the Gothic font productivity
and the numbers in this for that much bigger and much clearer next what we'll do is we'll see how the
increased font of text will select it and we increase form by clicking on this capital item or we can
just go to the numerical value of that font size and gender to the.
You can see that the front pages increased.
You can alter the greedy font size but the rest of our data
to Ben
next will play border to our daily borders give clarity to the dataset that we have.
Clearly the market data from that study watching these selected data go to this I can expand it into
Lake blue all time borders if you entered the data you can see that the small box it is containing our
data right now within our table if you want to have different kind of separating links you can accumulate
the data go to that item and to look more borders it you then select this tail off line that you want
to include and then get a a graphic box visible you can click and add borders
this evidence that we have added a dotted subediting line within our data and also there is a solid
box if you click on Okay
you can see that the within our table there is a dead are dotted land and altered the data that is just
altered box next we will change the color of our hair this is the little lady here says go to single
adoption and we will give it a late great live
activity.
A lot of stuff remains to light blue
we can see that some of these trends not name are not visible because of limited space in the column
A.
So now we will learn how to increase and decrease the column width and Rawhide.
First of all we don't click on the separator between and B column.
This will automatically increase the word tough column A to fit all the text in column A to do this
for all leaders and columns selectively they quarterly format option and select order column with
begin t see that it has to all the data in each column.
This is the maximum characters in that column we can see that total column is now very small.
Where is the percentage column in very large to manually correct this.
You can go to the separator between the and I would increase this
or you can also go to any format option and specify column Vic for this column.
Similarly high rate of growth can also be increased or decreased.
So for the average row you will increase the Grover slightly
next thing that we will do is they'll align our text so we'll first select all the headers that we have
and align them centrally.
Right now if you can see the total column in totality then on the left hand side this is because it
is left alone it will line it centrally total will move to center now will still remains and we will
align them to delay
only last through which is it contains average we can align text vertically also so to align this ending
vertically simple addition click on this I can just call the middle lane you can align text in top and
bottom alignment also added I don't know what it will be like first few rows on the top of the table
and one column on the left of the table
one shortcut to note here is once we have added a row to repeat that action you can use a shortcut just
do that at 40 whenever you tap DFAT Excel will repeat the last action.
Now let us first write the title of this table the same as the title of the selected read up on meeting
when the table contains 210 data plus the now you can see that the B three say it has more characters
that can fit into the column good that is why the text in that cell is going over into the next cell
to avoid that we can usually wrap text option when we click on that text the data within that cell moved
into the next line then that cell
if we add more X to that column it will automatically adjust and increase the height of that row to
accommodate the added text another option to accommodate when there is more text then the column it
is the center.
So we under that text that we adapt billiard a little silly all this in a little bit of this table will
let us much and center these alters you can see what happened is all this goes from B to J column have
been merged into a single T and the data ended and Beatle is now present in this it also what happened
all of the margins in the data was brilliant indeed.
Similarly a long position we clicked on left aligned to left the line this data you can keep it centrally
aligned if you like it that way.
Now let us move to the percentage column this column right now has fractions which had been calculated
by dividing total mark scored by each student with the maximum amount that can be scored in all these
six object to convert this fraction into percentages we select all these and click on the percentage
diagonal you can see that all these factions have not been converted to percentages.
Another thing that you like to do is in the last row which is the average middle we can see that there
are a lot of decimal points these numbers after the decimal point may not be delivered to us we may
just want to look at average to one decimal value let's select all these and go to the reduced decimal
values you're gonna total things like is wonderful really.
So now let us move on to conditional formatting conditional formatting is a very strong tool that Excel
providers.
This tool provides us the option to visually highlight the data as per the rules that we have specified.
This makes the data more understandable that by looking at it.
So let us highly in that example those two students were scored less than 35 marks in any of these object.
So we select all the numbers
then we go to conditional formatting and go to highlight to lose select less than value give a value
35 you can select several options here.
We will be selecting light red filled with dark red text you can see that four sets have been highlighted
for today to will score less than 35 marks in history to reinflate and there are two other failing student
in history.
All these have been highlighted at this option not being available you would have to manually find all
these individual tables and highlight them using the same color option.
There are other options in conditional formatting also.
So let us select the total column and let us look at what other options are available.
So if we go to the top bottom rules you can highlight the top 10 percent student visitor total Max magenta
color to green
intellect of the thing.
So Student 4 with maximum Marks has been highlighted.
Red scored 522 our top six and the other options available.
In addition formatting so we select the percentage column and you go to Dallas.
It will not be easy to visualize on the percentage column.
Let us go to the map column
and go to additional formatting data was you can see that each cell is free a buddy position of value
that they contain so a hundred is under potentially ninety nine point ninety nine percent.
45 is 45 percent.
Similarly now we will select the St. Mark to look at other options we go to those kids
the whole.
One of the options you can see that the colors are highlighted visited values.
So the highest is so in this example I used this Mauldin doctrine and the lowest is modern day and different
set of values have been highlighted in different colors.
Next we look at the items at we select the music scores for the conditional formatting and look at eye
contact.
If you go to the directional arrows they will separate the dataset into three parts.
The opportune time will have a potato the middle button data will be marked with yellow horizontal letter
and the last percentile will be marked with red arrows
on the percentage column.
Will try to know a player estimate.
So we go to conditional formatting applying new rule selector to color scale.
The lowest value will be basically
and highest will do the new will be Doug green okay.
Just when looking at the data you can see that student 1 and 2 and 4 probably the highest percentages
and student 2 student training will be having the lowest of percentages we look at some of the options
available and give you menu
and view menu you can see the data for checkbox 1 is played out there is one optional grid lines if
you uncheck it you'll see that the red line that only they will have been removed.
This particularly highlights the data they will.
And this is that contending data all the other says the multiple doses and mean.
If you take the former Labor deform labor are very visible to us but we like to see reform Labor so
really take it and take the headings option we see that the column names and raw numbers are in the
mode to we'll keep that until there there's a zoom option also.
But instead of using that I suggest that we should do zoom zoom in and do more from the bottom right
corner.
Next we look at an important tool which is free Spins free spins gives us three options
which are we can free the top row on the first column or the customized row and column that we want
to please what freezing a row or column does is whenever we scroll down.
We are not able to see the email headers for the rest of the data.
Similarly if we scroll to the late will not be able to see the student name in there are a lot of columns
in between.
So what we can do is we use these bend and we will use the first option to use this select the cell.
Top row and left column.
You want to fleece select excel both of these print and click on free speech.
You can see data suggesting lines it say that the top part of that line has been chosen and the left
part of that line has been chosen.
Now if you move your thoughts below the top in it is not moving all the devil and going under and if
you move it to the right the left part is not moving.
The other part is moving in the
so when you have a large set of data to maintain the table headers and table first column you can use
these being that's also deflected in the neglected we'll discussing about pivot tables.